﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;
using Entities;
using System.Net.Mail;

namespace Dao
{
    /// <summary>
    /// Provides a way of query with feedback from a SQL Server Database
    /// </summary>
    public class FeedbackData
    {
        #region Variables private
        private IList<Feedback> _listFeedback = null;
        private Feedback _fb = null;
        #endregion

        #region Methods public
        /// <summary>
        /// Display all information of Feedbacks
        /// </summary>
        /// <returns>List feedbacks</returns>
        public IList<Feedback> GetAll()
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.StoredProcedure, "sp_Feedback_GetAll", null);
                _listFeedback = new List<Feedback>();
                while (reader.Read())
                {
                    _fb = new Feedback();
                    if (!reader.IsDBNull(0))
                        _fb.FeedBackID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _fb.FullName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        _fb.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _fb.Tilte = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _fb.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _fb.SendDate = reader.GetDateTime(5);
                    _listFeedback.Add(_fb);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listFeedback;
        }
        /// <summary>
        /// Display details of a Feedback by id
        /// </summary>
        /// <param name="feedbackID">Id of Feedback want to view</param>
        /// <returns>Details of feedback</returns>
        public Feedback GetFeedbackByID(int feedbackID)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@FeedBackID", SqlDbType.Int, 2) };
                parm[0].Value = feedbackID;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.Text, "select * from FeedBacks where FeedBackID = @FeedBackID ", parm);
                while (reader.Read())
                {
                    _fb = new Feedback();
                    if (!reader.IsDBNull(0))
                        _fb.FeedBackID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _fb.FullName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        _fb.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _fb.Tilte = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _fb.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _fb.SendDate = reader.GetDateTime(5);
                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _fb;
        }
        public IList<Feedback> SearchByTitle(string tilte)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@Title", SqlDbType.NVarChar, 150) };
                parm[0].Value = tilte;
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.Text, "select * from Feedbacks where tilte like '%' +@Title+ '%'", parm);
                _listFeedback = new List<Feedback>();
                while (reader.Read())
                {
                    _fb = new Feedback();
                    if (!reader.IsDBNull(0))
                        _fb.FeedBackID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _fb.FullName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        _fb.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _fb.Tilte = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _fb.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _fb.SendDate = reader.GetDateTime(5);
                    _listFeedback.Add(_fb);

                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listFeedback;
        }
        public IList<Feedback> SortByColumn(string nameColumn)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            try
            {
                SqlDataReader reader = SQLDBHelper.ExecuteReader(conn, CommandType.Text, "select  * from FeedBacks order by " + nameColumn + " asc", null);
                _listFeedback = new List<Feedback>();
                while (reader.Read())
                {
                    _fb = new Feedback();
                    if (!reader.IsDBNull(0))
                        _fb.FeedBackID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        _fb.FullName = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        _fb.Email = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        _fb.Tilte = reader.GetString(3);
                    if (!reader.IsDBNull(4))
                        _fb.Content = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                        _fb.SendDate = reader.GetDateTime(5);
                    _listFeedback.Add(_fb);

                }
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return _listFeedback;
        }
        /// <summary>
        /// Insert or Update a Feedback
        /// If FeedbackID = 1 then insert new Feedback
        /// If FeedbackID = 0 then update Feedback
        /// </summary>
        /// <param name="fb">Colection properties of Feedbacks</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool UpDate(Feedback fb)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[]{
                    new SqlParameter("@FeedBackID", SqlDbType.Int, 2),
                    new SqlParameter("@FullName", SqlDbType.NVarChar, 200)
            };
                parms[0].Value = fb.FeedBackID;
                parms[1].Value = fb.FullName;

                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, "update Feedbacks set Fullname = @FullName where FeedBackID = @FeedBackID", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        public bool Insert(Feedback fb)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parms = new SqlParameter[]{
                    new SqlParameter("@FeedBackID", SqlDbType.Int, 2),
                    new SqlParameter("@FullName", SqlDbType.NVarChar, 200),
                    new SqlParameter("@Email", SqlDbType.NVarChar, 200),
                    new SqlParameter("@Tilte", SqlDbType.NVarChar, 200),
                    new SqlParameter("@Content", SqlDbType.NText),
                    new SqlParameter("@SendDate", SqlDbType.DateTime)
            };
                parms[0].Value = fb.FeedBackID;
                parms[1].Value = fb.FullName;
                parms[2].Value = fb.Email;
                parms[3].Value = fb.Tilte;
                parms[4].Value = fb.Content;
                parms[5].Value = fb.SendDate;

                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, "insert here", parms);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// Delete Feedback by ID
        /// </summary>
        /// <param name="feedBackID">ID of Feedback</param>
        /// <returns>True is successful , False is unsuccessful</returns>
        public bool DeleteById(int feedBackID)
        {
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            int result = 0;
            try
            {
                SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@FeedBackID", SqlDbType.Int, 2) };
                parm[0].Value = feedBackID;
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, "Delete from FeedBacks where FeedBackID = @FeedBackID", parm);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return result > 0 ? true : false;
        }
        /// <summary>
        /// Sends an e-mail message using the designated SMTP mail server.
        /// </summary>
        /// <param name="subject">The subject of the message being sent.</param>
        /// <param name="messageBody">The message body.</param>
        /// <param name="fromAddress">The sender's e-mail address.</param>
        /// <param name="toAddress">The recipient's e-mail address (separate multiple e-mail addresses
        /// with a semi-colon).</param>
        /// <param name="ccAddress">The address(es) to be CC'd (separate multiple e-mail addresses with
        /// a semi-colon).</param>
        /// <remarks>You must set the SMTP server within this method prior to calling.</remarks>
        /// <example>
        /// <code>
        ///   // Send a quick e-mail message
        ///   SendEmail.SendMessage("This is a Test", 
        ///                         "This is a test message...",
        ///                         "noboday@nowhere.com",
        ///                         "somebody@somewhere.com", 
        ///                         "ccme@somewhere.com");
        /// </code>
        /// </example>
        public void SendMail(string subject, string messageBody, string fromAddress, string toAddress, string ccAddress)
        {
            MailMessage message = new MailMessage();
            SmtpClient client = new SmtpClient();

            // Set the sender's address
            message.From = new MailAddress(fromAddress);

            // Allow multiple "To" addresses to be separated by a semi-colon
            if (toAddress.Trim().Length > 0)
                foreach (string addr in toAddress.Split(';'))
                    message.To.Add(new MailAddress(addr));

            // Allow multiple "Cc" addresses to be separated by a semi-colon
            if (ccAddress.Trim().Length > 0)
                foreach (string addr in ccAddress.Split(';'))
                    message.CC.Add(new MailAddress(addr));
            // Set the subject and message body text
            message.Subject = subject;
            message.Body = messageBody;

            // TODO: *** Modify for your SMTP server ***
            // Set the SMTP server to be used to send the message
            client.Host = "smtp.gmail.com"; // for example gmail smtp server
            client.EnableSsl = true;
            client.Credentials = new System.Net.NetworkCredential("doctorforumweb@gmail.com", "trantin2310");
            try
            {
                // Send the e-mail message
                client.Send(message);
            }
            catch (Exception ex)
            {
                ErrorManagement.TrackLog(ex.Message);
                throw ex;
            }
        }
        #endregion
    }
}
